﻿
#region | Copyright © Siriwat Suananpornpanit 2013 |

/*
	##########################################################################
		Software Generator Code For DAO From Table Name => USER
		Author By : Siriwat Suananpornpanit
		DateTime : 2013/09/12 00:02:04
	##########################################################################
	*/

#endregion

#region Class | Imports |

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Web.Dal;
using Web.Dao;
using Web.Prop;
using Microsoft.VisualBasic;

#endregion

namespace Web.Dao
{
    public class USERDao
    {

        /* for DataBase Connection */

        private static DBConnectionsEngine dbEngine;

        private static System.Data.SqlClient.SqlConnection Conn;

        private static Hashtable hashtable;


        private static USERDao _dao;

        private System.String sql = "";


        public static USERDao Init()
        {
            /* for Init */

            dbEngine = new DBConnectionsEngine();
            
            Conn = new ConnectionsSql().Connection; 

            _dao = new USERDao();

            return _dao;
        }

        public List<USER> Select()
        {
            List<USER> entities = new List<USER>();
            try
            {
                sql = System.String.Format(@" SELECT UID ,UWD ,PWD ,FNAME ,LNAME ,EMAIL ,TEL ,[POSITION] ,DEPT ,COMPANY ,CRDARE ,[UPDATE] ,LASTDATE ,LASTIP ,STATUS ,AUTH  FROM USERINFO");

                DataTable dt = dbEngine.GetDataTable(sql, Conn);

                entities = (from o in dt.AsEnumerable()
                            select new USER()
                            {
                                UID = o.Field<System.String>("UID")
                                 ,
                                UWD = o.Field<System.String>("UWD")
                                 ,
                                PWD = o.Field<System.String>("PWD")
                                 ,
                                FNAME = o.Field<System.String>("FNAME")
                                 ,
                                LNAME = o.Field<System.String>("LNAME")
                                 ,
                                EMAIL = o.Field<System.String>("EMAIL")
                                 ,
                                TEL = o.Field<System.String>("TEL")
                                 ,
                                POSITION = o.Field<System.String>("POSITION")
                                 ,
                                DEPT = o.Field<System.String>("DEPT")
                                 ,
                                COMPANY = o.Field<System.String>("COMPANY")
                                 ,
                                CRDARE = o.Field<System.DateTime>("CRDARE")
                                 ,
                                UPDATE = o.Field<System.DateTime>("UPDATE")
                                 ,
                                LASTDATE = o.Field<System.DateTime>("LASTDATE")
                                 ,
                                LASTIP = o.Field<System.String>("LASTIP")
                                 ,
                                STATUS = o.Field<System.String>("STATUS")
                                 ,
                                AUTH = o.Field<System.String>("AUTH")
                            }).ToList<USER>();

            }
            catch (Exception ex)
            {
                entities = null;
            }
            return entities;
        }

        public List<Prop.USER> Select(Prop.USER obj)
        {
            List<Prop.USER> entities = new List<Prop.USER>();
            try
            {
                System.Text.StringBuilder concast = new System.Text.StringBuilder();

                concast.Append(" WHERE 1=1 ");

               // hashtable = new Hashtable();

                if (obj != null)
                {
                    if (!String.IsNullOrEmpty(obj.UID))
                    {
                        concast.AppendFormat(" AND UID='{0}' ", obj.UID);
                    }

                    if (!String.IsNullOrEmpty(obj.UWD))
                    {
                        concast.AppendFormat(" AND UWD='{0}' ", obj.UWD);
                    }

                    if (!String.IsNullOrEmpty(obj.PWD))
                    {
                        concast.AppendFormat(" AND PWD='{0}' ", obj.PWD);
                    }

                    if (!String.IsNullOrEmpty(obj.EMAIL))
                    {
                        concast.AppendFormat(" AND EMAIL='{0}' ",obj.EMAIL);
                    }
                    /*
                    if (!String.IsNullOrEmpty(obj.FNAME))
                    {
                        concast.Append(" AND FNAME=":FNAME" ");
                        hashtable.Add(":FNAME", obj.FNAME);
                    }

                    if (!String.IsNullOrEmpty(obj.LNAME))
                    {
                        concast.Append(" AND LNAME=":LNAME" ");
                        hashtable.Add(":LNAME", obj.LNAME);
                    }
                    */
                    /*
                    if (!String.IsNullOrEmpty(obj.TEL))
                    {
                        concast.Append(" AND TEL=":TEL" ");
                        hashtable.Add(":TEL", obj.TEL);
                    }

                    if (!String.IsNullOrEmpty(obj.POSITION))
                    {
                        concast.Append(" AND POSITION=":POSITION" ");
                        hashtable.Add(":POSITION", obj.POSITION);
                    }

                    if (!String.IsNullOrEmpty(obj.DEPT))
                    {
                        concast.Append(" AND DEPT=":DEPT" ");
                        hashtable.Add(":DEPT", obj.DEPT);
                    }

                    if (!String.IsNullOrEmpty(obj.COMPANY))
                    {
                        concast.Append(" AND COMPANY=":COMPANY" ");
                        hashtable.Add(":COMPANY", obj.COMPANY);
                    }

                    if (obj.STATUS != (Int32)Core.Status.Use)
                    {
                        concast.AppendFormat(" AND STATUS='1' ", obj.STATUS);
                    }
                    */

                }
                sql = System.String.Format(@" SELECT UID ,UWD ,PWD ,FNAME ,LNAME ,EMAIL ,TEL ,[POSITION] ,DEPT ,COMPANY ,CRDARE ,[UPDATE] ,LASTDATE ,LASTIP ,[STATUS] ,AUTH  FROM USERINFO");

                sql = sql + concast.ToString();

                entities = dbEngine.ToList<Prop.USER>(sql, Conn).ToList();

            }
            catch (Exception ex)
            {
                entities = null;
            }
            return entities;
        }


        public List<Prop.USER> SelectCondition(Prop.USER obj)
        {
            List<Prop.USER> entities = new List<Prop.USER>();
            try
            {
                System.Text.StringBuilder concast = new System.Text.StringBuilder();

                concast.Append(" WHERE 1=1 AND AUTH <> '1' ");

                // hashtable = new Hashtable();

                if (obj != null)
                {
                    if (!String.IsNullOrEmpty(obj.FNAME))
                    {
                        concast.AppendFormat(" AND FNAME LIKE '%{0}%' ", obj.FNAME);
                    }

                    if (!String.IsNullOrEmpty(obj.LNAME))
                    {
                        concast.AppendFormat(" AND LNAME LIKE '%{0}%' ", obj.LNAME);
                    }

                    if (!String.IsNullOrEmpty(obj.EMAIL))
                    {
                        concast.AppendFormat(" AND EMAIL LIKE '%{0}%' ", obj.EMAIL);
                    }
                    /*
                    if (!String.IsNullOrEmpty(obj.TEL))
                    {
                        concast.Append(" AND TEL=":TEL" ");
                        hashtable.Add(":TEL", obj.TEL);
                    }
                    */
                    if (!String.IsNullOrEmpty(obj.POSITION))
                    {
                        concast.AppendFormat(" AND POSITION LIKE '%{0}%' ", obj.POSITION);
                    }

                    if (!String.IsNullOrEmpty(obj.DEPT))
                    {
                        concast.AppendFormat(" AND DEPT LIKE '%{0}%' ", obj.DEPT);
                    }

                    if (!String.IsNullOrEmpty(obj.COMPANY))
                    {
                        concast.AppendFormat(" AND COMPANY LIKE '%{0}%' ", obj.COMPANY);
                    }
                    /*
                    if (obj.STATUS != (Int32)Core.Status.Use)
                    {
                        concast.AppendFormat(" AND STATUS='1' ", obj.STATUS);
                    }
                    */

                }
                sql = System.String.Format(@" SELECT UID ,UWD ,PWD ,FNAME ,LNAME ,EMAIL ,TEL ,[POSITION] ,DEPT ,COMPANY ,CRDARE ,[UPDATE] ,LASTDATE ,LASTIP ,[STATUS] ,AUTH  FROM USERINFO");

                sql = sql + concast.ToString();

                entities = dbEngine.ToList<Prop.USER>(sql, Conn).ToList();

            }
            catch (Exception ex)
            {
                entities = null;
            }
            return entities;
        }

        public List<Prop.USER> AuthorizeSelect()
        {
            List<Prop.USER> entities = new List<Prop.USER>();
            try
            {
                System.Text.StringBuilder concast = new System.Text.StringBuilder();

                concast.Append(" WHERE 1=1 AND AUTH <> '1' ");

                // hashtable = new Hashtable();
                
                sql = System.String.Format(@" SELECT UID ,UWD ,PWD ,FNAME ,LNAME ,EMAIL ,TEL ,[POSITION] ,DEPT ,COMPANY ,CRDARE ,[UPDATE] ,LASTDATE ,LASTIP ,[STATUS] ,AUTH  FROM USERINFO");

                sql = sql + concast.ToString();

                entities = dbEngine.ToList<Prop.USER>(sql, Conn).ToList();

            }
            catch (Exception ex)
            {
                entities = null;
            }
            return entities;
        }

        public List<Prop.USER> ReportLoginSelect()
        {
            List<Prop.USER> entities = new List<Prop.USER>();
            try
            {
                System.Text.StringBuilder concast = new System.Text.StringBuilder();

                concast.Append(" WHERE 1=1 AND AUTH <> '1' ");

                // hashtable = new Hashtable();

                sql = System.String.Format(@" SELECT UID ,UWD ,PWD ,FNAME ,LNAME ,EMAIL ,TEL ,[POSITION] ,DEPT ,COMPANY ,CRDARE ,[UPDATE] ,LASTDATE ,LASTIP ,[STATUS] ,AUTH  FROM USERINFO");

                sql = sql + concast.ToString();

                entities = dbEngine.ToList<Prop.USER>(sql, Conn).ToList();

            }
            catch (Exception ex)
            {
                entities = null;
            }
            return entities;
        }

        public Boolean Add(USER model)
        {
            Boolean result = false;
            try
            {
                sql = System.String.Format(@" INSERT INTO 
[USERINFO](UID ,UWD ,PWD ,FNAME ,LNAME ,EMAIL ,TEL ,POSITION ,DEPT 
,COMPANY ,CRDARE ,LASTIP ,[STATUS] ,AUTH ) 
VALUES('{0}' ,'{1}' ,'{2}' ,'{3}' ,'{4}' ,'{5}' ,'{6}' ,'{7}' ,'{8}' 
,'{9}' ,'{10}' ,'{11}' ,'{12}' ,'{13}')",
                    model.UID
                     ,
                    model.UWD
                     ,
                    model.PWD
                     ,
                    model.FNAME
                     ,
                    model.LNAME
                     ,
                    model.EMAIL
                     ,
                    model.TEL
                     ,
                    model.POSITION
                     ,
                    model.DEPT
                     ,
                    model.COMPANY
                     ,
                    model.CRDARE
                     ,
                    model.LASTIP
                     ,
                    model.STATUS.ToString()
                     ,
                    model.AUTH.ToString()
                     );

                dbEngine.ExecuteNonQuery(sql, Conn);

            }
            catch (Exception ex)
            {
                result = true;
            }
            return result;
        }

        public Boolean Update(USER model)
        {
            Boolean result = false;
            try
            {
                sql = System.String.Format(@" UPDATE [USERINFO]
					SET
					FNAME='{1}' ,
					LNAME='{2}' ,
					EMAIL='{3}' ,
					TEL='{4}' ,
					[POSITION]='{5}' ,
					DEPT='{6}',
					COMPANY='{7}' ,
					[UPDATE]='{8}' ,
                    PWD ='{9}',
                    UWD = '{10}'
                    WHERE UID='{0}'
					",
                    model.UID
                     ,
                    model.FNAME
                     ,
                    model.LNAME
                     ,
                    model.EMAIL
                     ,
                    model.TEL
                     ,
                    model.POSITION
                     ,
                    model.DEPT
                     ,
                    model.COMPANY
                     ,
                    model.UPDATE
                    ,
                    model.PWD
                    ,
                    model.UWD
                     );

                dbEngine.ExecuteNonQuery(sql, Conn);

            }
            catch (Exception ex)
            {
                result = true;
            }
            return result;
        }

        public Boolean Udate(String UID,DateTime update, String lastIp)
        {
            Boolean result = false;
            try
            {
                sql = System.String.Format(@" UPDATE [USERINFO]
					SET
					LASTDATE ='{1}' ,
                    LASTIP='{2}' 
                    WHERE UID='{0}'
					", UID, update, lastIp);

                dbEngine.ExecuteNonQuery(sql, Conn);

            }
            catch (Exception ex)
            {
                result = true;
            }
            return result;
        }

        public Boolean StatusUpdate(String UID, String Status)
        {
            Boolean result = false;
            try
            {
                sql = System.String.Format(@" UPDATE [USERINFO]
					SET
					[STATUS]='{1}' 
                    WHERE UID='{0}'
					", UID, Status);

                dbEngine.ExecuteNonQuery(sql, Conn);

            }
            catch (Exception ex)
            {
                result = true;
            }
            return result;
        }

        public Boolean Delete(String UID)
        {
            Boolean result = false;
            try
            {
                sql = System.String.Format(@" DELETE FROM [USER] WHERE UID='{0}' ",UID);

                dbEngine.ExecuteNonQuery(sql, Conn);

            }
            catch (Exception ex)
            {
                result = true;
            }
            return result;
        }

    }
}

/*
##########################################################################
    Free Software License
##########################################################################
*/
